/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：返佣提现记录接口实现                                                    
*│　作    者：gaotang                                            
*│　版    本：1.0    模板代码自动生成                                                
*│　创建时间：2019-02-15 13:42:39                             
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： BestTrader.Cms.Repository.SqlServer                                  
*│　类    名： RebateExtractRepository                                      
*└──────────────────────────────────────────────────────────────┘
*/
using BestTrader.Cms.Core.DbHelper;
using BestTrader.Cms.Core.Extensions;
using BestTrader.Cms.Core.Options;
using BestTrader.Cms.Core.Repository;
using BestTrader.Cms.IRepository;
using BestTrader.Cms.Models;
using Dapper;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;

namespace BestTrader.Cms.Repository.SqlServer
{
    public class RebateExtractRepository : BaseRepository<RebateExtract, Int32>, IRebateExtractRepository
    {
        public RebateExtractRepository(IOptionsSnapshot<DbOption> options)
        {
            _dbOption = options.Get("BT.TEST");
            if (_dbOption == null)
            {
                throw new ArgumentNullException(nameof(DbOption));
            }
            _dbConnection = ConnectionFactory.CreateConnection(_dbOption.DbType, _dbOption.ConnectionString);
        }

        /// <summary>
        /// 获取成本
        /// </summary>
        /// <returns></returns>
        public Decimal GetTotalCostByManagerId(string UserId, DateTime StartDate, DateTime EndDate, bool IsAdmin)
        {
            string conditions = "";
            if (IsAdmin)
            {
                //conditions = $"and AccountName in(select UserName + '01' from Manager where (Id = '{UserId}' or AddManagerId = '{UserId}' ) and IsDelete = 0)";

                string sql = $@"SELECT  ISNULL(SUM(ExtractRMBMoney),0) as ExtractRMBMoney FROM [RebateExtract] where ExtractRMBType = 1 and IsDelete = 0 and ManagerId in(select Id from Manager where RoleId = 3 and IsDelete = 0)
                            and DealedNumber in(select DealedNumber from History_DealedOrderReport where DealedDate >= '{StartDate}' and DealedDate <= '{EndDate}' 
             {conditions} )";

                return _dbConnection.QueryFirstOrDefault<Decimal>(sql);
            }
            else
            {
                var UserNames = _dbConnection.GetList<Manager>($"WHERE  (Id = '{UserId}' or AddManagerId = '{UserId}' ) and IsDelete = 0  ").ToList();
                conditions = $"and AccountName in({ String.Join(',', UserNames.Select(p => $"'{p.UserName}01'"))})";
                var historySql = $"select DealedNumber from [BT.TEST].[dbo].[History_DealedOrderReport] where DealedDate >= '{StartDate}' and DealedDate <= '{EndDate}' { conditions}";
                var DealedNumbers = _dbConnection.Query<History_DealedOrderReport>(historySql);
                if (DealedNumbers.Count() > 0)
                {
                    string sql = $@"SELECT  ISNULL(SUM(ExtractRMBMoney),0) as ExtractRMBMoney FROM [BT.TEST].[dbo].[RebateExtract] where ExtractRMBType = 1 and IsDelete = 0 and ManagerId in(select Id from [BT.TEST].[dbo].[Manager] where RoleId = 3 and IsDelete = 0)
                                            and DealedNumber in({string.Join(',', DealedNumbers.Select(p => $"'{p.DealedNumber}'"))})";

                    return _dbConnection.QueryFirstOrDefault<Decimal>(sql);
                }
                else {
                    return 0;
                }

            }
        }


        /// <summary>
        /// 获取成本2
        /// </summary>
        /// <returns></returns>
        public Decimal GetTotalCostNewFun(string UserId, DateTime StartDate, DateTime EndDate, bool IsAdmin)
        {
            string conditions = "";
            if (IsAdmin)
            {
                //conditions = $"and AccountName in(select UserName + '01' from Manager where (Id = '{UserId}' or AddManagerId = '{UserId}' ) and IsDelete = 0)";

                string sql = $@"SELECT  ISNULL(SUM(ExtractRMBMoney),0) as ExtractRMBMoney FROM [RebateExtract] where ExtractRMBType = 1 and IsDelete = 0 and ManagerId in(select Id from Manager where RoleId = 3 and IsDelete = 0)
                            and DealedNumber in(select DealedNumber from History_DealedOrderReport where DealedDate >= '{StartDate}' and DealedDate <= '{EndDate}' 
             {conditions} )";

                return _dbConnection.QueryFirstOrDefault<Decimal>(sql);
            }
            else
            {
                var userIds = GetChildAndSelfManagerIds(UserId.ToInt32().Value);
                string sql = $@"SELECT  ISNULL(SUM(t1.ExtractRMBMoney),0) as ExtractRMBMoney FROM [BT.TEST].[dbo].[RebateExtract] as t1  INNER JOIN [BT.TEST].[dbo].[History_DealedOrderReport] as t2 ON t1.DealedNumber=t2.DealedNumber 
                            WHERE t1.ExtractRMBType = 1 and t1.IsDelete = 0 AND t2.DealedDate >= '{StartDate}' AND t2.DealedDate <= '{EndDate}' AND  t1.ManagerId in(select Id from [BT.TEST].[dbo].[Manager] where RoleId = 3 and IsDelete = 0)
                                            And  t1.AddManagerId IN({string.Join(',', userIds.Select(p => p))})";
                return _dbConnection.QueryFirstOrDefault<Decimal>(sql);

            }
        }

        public IEnumerable<int> GetChildAndSelfManagerIds(int id)
        {
            //服务器范围的默认值为 100。如果指定 0，则没有限制。每一个语句只能指定一个 MAXRECURSION 值使用类似的语句：
            string sql = @"WITH cteTree
	                        AS (SELECT * FROM Manager
			                        WHERE IsDelete = 0 and Id = @Id  --第一个查询作为递归的基点(锚点)
		                        UNION ALL
		                        SELECT Manager.*     --第二个查询作为递归成员， 下属成员的结果为空时，此递归结束。
			                        FROM cteTree INNER JOIN Manager ON cteTree.Id = Manager.AddManagerId where Manager.IsDelete = 0) 
	                        SELECT Id FROM cteTree OPTION (MAXRECURSION 0);";
            return _dbConnection.Query<int>(sql, new
            {
                Id = id
            });
        }

        //        string conditions = "";
        //            if (!IsAdmin)
        //            {
        //               var UserNames = _dbConnection.GetList<Manager>($"WHERE  (Id = '{UserId}' or AddManagerId = '{UserId}' ) and IsDelete = 0  ").ToList();
        //        conditions = $"and AccountName in({ String.Join(',', UserNames.Select(p => $"'{p.UserName}01'"))})";
        //            }
        //    var historySql = $"select DealedNumber from [BT.TEST].[dbo].[History_DealedOrderReport] where DealedDate >= '{StartDate}' and DealedDate <= '{EndDate}' { conditions}";
        //    var DealedNumbers = _dbConnection.Query<History_DealedOrderReport>(historySql).Select(p => p.DealedNumber);

        //    string sql = $@"SELECT  ISNULL(SUM(ExtractRMBMoney),0) as ExtractRMBMoney FROM [BT.TEST].[dbo].[RebateExtract] where ExtractRMBType = 1 and IsDelete = 0 and ManagerId in(select Id from [BT.TEST].[dbo].[Manager] where RoleId = 3 and IsDelete = 0)
        //                            and DealedNumber in @DealedNumber";

        //            return _dbConnection.QueryFirstOrDefault<Decimal>(sql, new { DealedNumber  = DealedNumbers
        //});


    }
}